package com.fs.mybatis.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.fs.mybatis.core.generator.config.JDBCConnectionConfiguration;
import com.fs.mybatis.core.generator.internal.db.ConnectionFactory;
import com.fs.mybatis.domain.Table;
import com.google.common.collect.Lists;

public class GeneralCodeService {
    protected Connection con;

    protected Connection getConnection(JDBCConnectionConfiguration jdbcConnectionConfiguration) throws SQLException {
        Connection connection = ConnectionFactory.getInstance().getConnection(jdbcConnectionConfiguration);
        return connection;
    }

    protected void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    protected List<Table> getOracleTables(String url, String usr, String pas) throws ClassNotFoundException,
            SQLException {
        ResultSet rs = null;
        List<Table> tables = Lists.newArrayList();
        Class.forName("oracle.jdbc.driver.OracleDriver");
        try {
            con = DriverManager.getConnection(url, usr, pas);
            PreparedStatement st = con.prepareStatement(getOracleSql(usr));
            // 查询，得出结果集
            rs = st.executeQuery();
            while (rs.next()) {
                Table table =
                        new Table(rs.getString("TABLE_NAME"), rs.getString("COMMENTS"), rs.getString("COLUMN_NAME"));
                tables.add(table);
            }
            con.close();
        } catch (SQLException e) {
            con.close();
            e.printStackTrace();
        }
        return tables;
    }

    protected List<Table> getMysqlTables(String url, String usr, String pas) throws ClassNotFoundException,
            SQLException {
        ResultSet rs = null;
        List<Table> tables = Lists.newArrayList();
        Class.forName("com.mysql.jdbc.Driver");
        try {
            con = DriverManager.getConnection(url, usr, pas);
            PreparedStatement st = con.prepareStatement(getMysqlSql());
            // 查询，得出结果集
            rs = st.executeQuery();
            while (rs.next()) {
                Table table =
                        new Table(rs.getString("TABLE_NAME"), rs.getString("TABLE_COMMENT"), rs.getString("COLUMN_NAME"));
                tables.add(table);
            }
            con.close();
        } catch (SQLException e) {
            con.close();
            e.printStackTrace();
        }
        return tables;
    }

    protected String getDatabaseType(String url) {
        if (url.contains("oracle")) {
            return "oracle";
        } else if (url.contains("mysql")) {
            return "mysql";
        }
        return null;
    }

    private String getOracleSql(String user) {
        String sql =
                "SELECT T.TABLE_NAME, nvl(T.COMMENTS,' ') COMMENTS, C.CONSTRAINT_NAME, nvl(CC.COLUMN_NAME,' ')COLUMN_NAME"
                        + " FROM USER_TAB_COMMENTS T LEFT JOIN USER_CONSTRAINTS C "
                        + " ON T.TABLE_NAME = C.TABLE_NAME AND C.CONSTRAINT_TYPE = 'P' AND C.OWNER = '"
                        + user.toUpperCase()
                        + "'"
                        + " LEFT JOIN USER_CONS_COLUMNS CC ON CC.OWNER = 'SYW' AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME"
                        + " WHERE T.TABLE_TYPE = 'TABLE'";
        System.out.println(sql);
        return sql;
    }

    private String getMysqlSql() {
        String sql = "SELECT t.TABLE_NAME,ta.TABLE_COMMENT, c.COLUMN_NAME FROM information_schema.TABLES ta,"
                + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,"
                + " INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c WHERE "
                + " t.TABLE_NAME = c.TABLE_NAME AND ta.TABLE_NAME=t.TABLE_NAME "
                + " AND t.TABLE_SCHEMA = 'mcms' AND t.CONSTRAINT_TYPE = 'PRIMARY KEY'";
        return sql;
    }
}
